﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;

namespace Quản_Lý_Thư_Viện
{
    class DocgiaDataAccess
    {
        private DataProvider _provider = new DataProvider();
        public DocgiaDataAccess()
        {
            _provider.connect();
        }
        public void insert(DocgiaInfo info)
        {
            DataTable dt = new DataTable();
            dt = LayDSdocgia();
            int i = dt.Rows.Count+1;
            string ngaylamthe = info.Nglamthe.Value.Month + "/" + info.Nglamthe.Value.Day + "/" + info.Nglamthe.Value.Year;
            string ngayhethan = info.Nghethan.Value.Month + "/" + info.Nghethan.Value.Day + "/" + info.Nghethan.Value.Year;
            string ngaysinh = info.Ngsinh.Value.Month + "/" + info.Ngsinh.Value.Day + "/" + info.Ngsinh.Value.Year;
            string insertCommand = "insert into dbo.DOCGIA(MADG,MATAIKHOAN,MALOAITHE, HOTEN, NGSINH, CMND, DIACHI,NGHENGHIEP,NOILAMVIEC,DIENTHOAI,NGLAMTHE,NGHETHAN,TRANGTHAI,SOTIENNO,GIOITINH) values('DG" + i + "','" + info.Mataikhoan + "'," + "N'" + info.Maloaithe + "'," + "N'"
                                + info.Hoten + "'," + "N'"
                                + ngaysinh + "','" + info.Cmnd + "','" 
                                + info.Diachi +"', " +"N'"+ info.Nghenghiep + "'," +"N'"
                                + info.Noilamviec +"'," +"N'" +info.Dienthoai +"'," +"N'"
                                + ngaylamthe + "','" + ngayhethan + "','"
                                 + info.Trangthai + "', " + "N'" + info.Sotienno + "'," + "N'"
                                + info.Gioitinh +
                                 "')";

            _provider.executeNonQuery(insertCommand);
        }

        public void update(DocgiaInfo info)
        {

            string ngaylamthe = info.Nglamthe.Value.Month + "/" + info.Nglamthe.Value.Day + "/" + info.Nglamthe.Value.Year;
            string ngayhethan = info.Nghethan.Value.Month + "/" + info.Nghethan.Value.Day + "/" + info.Nghethan.Value.Year;
            string ngaysinh = info.Ngsinh.Value.Month + "/" + info.Ngsinh.Value.Day + "/" + info.Ngsinh.Value.Year;
            string updateCommand = "Update dbo.DOCGIA Set MALOAITHE= N'"
                            + info.Maloaithe + "', HOTEN = N'" + info.Hoten
                            + "', NGSINH = '" + ngaysinh + "', CMND = '" + info.Cmnd
                            + "', DIACHI  = N'" + info.Diachi
                            + "', NGHENGHIEP  = N'" + info.Nghenghiep
                            + "', NOILAMVIEC  = N'" + info.Noilamviec
                            + "', DIENTHOAI  = N'" + info.Dienthoai
                            + "', NGLAMTHE  = '" + ngaylamthe
                            + "', NGHETHAN  = N'" + ngayhethan
                            + "', SOTIENNO  = '" + info.Sotienno
                            + "', TRANGTHAI  = N'" + info.Trangthai
                            + "', GIOITINH  = N'" + info.Gioitinh
                            + "' Where MADG = '" + info.Madocgia + "'";

            _provider.executeNonQuery(updateCommand);
        }
        public void updatestn()
        {
            string updatecommand = "Update dbo.DOCGIA set SOTIENNO = '" + frmTraTien.stn + "' where MADG = '" + frmTraTien.mdgmp + "'";
            _provider.executeNonQuery(updatecommand);
        }
        public void updatetrangthai()
        {
            string updateCommand = "Update dbo.DOCGIA set TRANGTHAI = 'Vi Pham' where MADG = '" +frmTraTien.mdgmp+ "'";
            _provider.executeNonQuery(updateCommand);
        }
        public void updatetrangthaikvp()
        {
            string updateCommand = "Update dbo.DOCGIA set TRANGTHAI = 'Ko Vi Pham' where MADG = '" + frmTraTien.mdgmp + "'";
            _provider.executeNonQuery(updateCommand);
        }
        public void delete(DocgiaInfo info)
        {
            string deleteCommand = "DELETE FROM dbo.DOCGIA WHERE MADG = '" + info.Madocgia + "'";
            _provider.executeNonQuery(deleteCommand);
        }
        public DataTable LayDSdocgia()
        {
            return _provider.GetData("select MADG,dbo.DOCGIA.MATAIKHOAN,MALOAITHE, HOTEN, NGSINH, CMND, DIACHI,NGHENGHIEP,NOILAMVIEC,DIENTHOAI,NGLAMTHE,NGHETHAN,TRANGTHAI,SOTIENNO,GIOITINH,MATKHAU from dbo.DOCGIA , dbo.PHANQUYEN where dbo.DOCGIA.MATAIKHOAN = dbo.PHANQUYEN.MATAIKHOAN order by MADG desc");
        }
        public DataTable chomuonsach(DocgiaInfo info)
        {
            return _provider.GetData("select * from dbo.DOCGIA where MADG = '" + info.Madocgia + "'");
        }
        public DataTable Laymataikhoan()
        {
            return _provider.GetData("select MADG,dbo.DOCGIA.MATAIKHOAN,MALOAITHE, HOTEN, NGSINH, CMND, DIACHI,NGHENGHIEP,NOILAMVIEC,DIENTHOAI,NGLAMTHE,NGHETHAN,TRANGTHAI,SOTIENNO,GIOITINH,MATKHAU from dbo.DOCGIA,dbo.PHANQUYEN where dbo.DOCGIA.MATAIKHOAN = '" + FrmDangNhap.txt1 + "' and dbo.DOCGIA.MATAIKHOAN = dbo.PHANQUYEN.MATAIKHOAN ");
        }
        public DataTable Laysotienno()
        {
            return _provider.GetData("Select SOTIENNO from dbo.DOCGIA where MADG = '" + frmPhieuTraSach.madg1 + "'");
        }
        public DataTable Laystnptt()
        {
            return _provider.GetData("select SOTIENNO from dbo.DOCGIA where MADG = '" + frmTraTien.mdgmp + "'");
        }
        public DataTable timdocgia()
        {
            return _provider.GetData("select dbo.docgia.hoten,dbo.loaithe.tenloaithe,dbo.docgia.nghethan from dbo.Docgia,dbo.loaithe where dbo.loaithe.maloaithe = dbo.docgia.maloaithe and dbo.docgia.madg = '" + frmGiaHan.madg + "'");
        }
        public DataTable timdg()
        {
            return _provider.GetData("select MADG,dbo.DOCGIA.MATAIKHOAN,MALOAITHE, HOTEN, NGSINH, CMND, DIACHI,NGHENGHIEP,NOILAMVIEC,DIENTHOAI,NGLAMTHE,NGHETHAN,TRANGTHAI,SOTIENNO,GIOITINH,MATKHAU from dbo.DOCGIA,dbo.PHANQUYEN where dbo.DOCGIA.MATAIKHOAN =dbo.PHANQUYEN.MATAIKHOAN and HOTEN like '" + frmChinh.timdg + "%'");
        }
        public void ghthe()
        {
            string updatecommand = ("update dbo.docgia set nghethan = dateadd(month," + frmGiaHan.sothangghthe + ",nghethan)  where (dbo.docgia.madg = '" + frmGiaHan.madg + "')");
            _provider.executeNonQuery(updatecommand);
        }
        public DataTable timsach()
        {
            return _provider.GetData("select dbo.dausach.tensach,dbo.phieumuonsach.ngtraqd from dbo.dausach,dbo.sach,dbo.phieumuonsach,dbo.ct_pms where dbo.dausach.madausach = dbo.sach.madausach and dbo.sach.masach = dbo.ct_pms.masach and dbo.ct_pms.mapms = dbo.phieumuonsach.mapms and dbo.phieumuonsach.madg = '" + frmGiaHan.madg + "' and dbo.sach.masach = '" + frmGiaHan.masach + "'");
        }
        public DataTable timsachmp()
        {
            return _provider.GetData("select dbo.CT_PMS.MASACH, dbo.phieumuonsach.ngtraqd from  dbo.CT_PMS,dbo.PHIEUMUONSACH where CT_PMS.MAPMS= PHIEUMUONSACH.MAPMS and phieumuonsach.mapms = '" + frmGiaHan.mapm + "'");
        }
       
    }
}
